import json
import math
import sqlite3
import time
from datetime import datetime
from model.EventModel import EventModel
from sqlalchemy import text
import matplotlib.pyplot as plt
import io


def to_timestamp(time_str, fmt='%m-%d-%Y %H:%M:%S'):
    time_tuple = time.strptime(time_str, fmt)
    timestamp = time.mktime(time_tuple)
    return timestamp


# 给定一个日期检查是不是周末
def is_weekend(date_str):
    date_obj = datetime.strptime(date_str, '%m-%d-%Y').date()
    # 判断是否为周末
    if date_obj.weekday() in [6]:
        return True
    else:
        return False
    pass


def is_holiday(date_str):
    with open('utils/au.json', 'r') as f:
        result = False
        data = json.load(f)
        date_obj = datetime.strptime(date_str, '%m-%d-%Y').date()
        s = date_obj.strftime('%Y-%m-%d')

        for item in data:
            if s == item.get('date'):
                result = item.get('name')
        return result


class EventDao:
    def __init__(self, db):

        self.db = db
        pass

    def create(self, object={}):
        try:
            # 检查日期时间是否冲突
            if self.__checkDateCross(object):
                raise Exception("Time conflict")
            event = EventModel()
            event.name = object.get('name')
            event.date = object.get('date')
            event._date = int(to_timestamp(object.get('date') + " 00:00:00"))
            event.from_time = to_timestamp(object.get('date') + " " + object.get("from"))
            event.to_time = to_timestamp(object.get('date') + " " + object.get("to"))
            event.description = object.get('description')
            event.location = json.dumps(object.get('location'))
            self.db.session.add(event)
            self.db.session.commit()
            # 转换成年月日时分秒
            datetime_obj = datetime.strptime(str(event.last_update), '%Y-%m-%d %H:%M:%S.%f')
            formatted_date = datetime_obj.strftime('%Y-%m-%d %H:%M:%S')
            return {
                "code": 200,
                "data": {
                    "id": str(event.id),
                    "last_update": formatted_date,
                    "_links": {
                        "self": {
                            "href": "/events/" + str(event.id)
                        }
                    }
                }
            }
        except Exception as e:
            return {
                "code": 400,
                "message": str(e)
            }
            pass

    def delete(self, id):
        try:
            event = self.db.session.get(EventModel, id)
            if event:
                self.db.session.delete(event)
                self.db.session.commit()
                return {
                    "code": 200,
                    "data": {
                        "id": id,
                        "message": f"The event with id {id} was removed from the database!"
                    }
                }
            pass
            raise Exception("no record")
        except Exception as e:
            return {
                "code": 400,
                "message": str(e)
            }
            pass

    def update(self, id, data):
        try:
            # 检查记录是否存在
            event = self.db.session.get(EventModel, id)
            # 记录不存在报错
            if not event:
                raise Exception("no record")
            # 获取JSON过来的数据
            data['id'] = id
            data['date'] = event.date
            if self.__checkDateCross1(data):
                raise Exception("Time conflict")
            event.from_time = to_timestamp(event.date + " " + data.get("from"))
            event.to_time = to_timestamp(event.date + " " + data.get("to"))
            event.description = data.get('description')
            event.verified = True
            self.db.session.commit()
            # 转换成年月日时分秒
            datetime_obj = datetime.strptime(str(event.last_update), '%Y-%m-%d %H:%M:%S.%f')
            formatted_date = datetime_obj.strftime('%Y-%m-%d %H:%M:%S')
            return {
                "code": 200,
                "data": {
                    "id": str(event.id),
                    "last_update": formatted_date,
                    "_links": {
                        "self": {
                            "href": "/events/" + str(event.id)
                        }
                    }
                }
            }
        except Exception as e:
            return {
                "code": 400,
                "message": str(e)
            }
            pass

    def get(self, id):
        try:
            # 根据id查询数据表记录
            stmt = self.db.select(EventModel).where(EventModel.id == id)
            result = self.db.session.execute(stmt).scalar_one_or_none()
            if result:
                datetime_obj = datetime.strptime(str(result.last_update), '%Y-%m-%d %H:%M:%S.%f')
                formatted_date = datetime_obj.strftime('%Y-%m-%d %H:%M:%S')
                response_data = {
                    "id": result.id,
                    "last-update": formatted_date,
                    "name": result.name,
                    "date": result.date,
                    "from": datetime.fromtimestamp(result.from_time).strftime("%H:%M:%S"),
                    "to": datetime.fromtimestamp(result.to_time).strftime("%H:%M:%S"),
                    "location": json.loads(result.location),
                    "_metadata": {
                        "wind-speed": "",
                        "weather": "",
                        "humidity": "",
                        "temperature": "",
                        "holiday": is_holiday(result.date),
                        "weekend": is_weekend(result.date)
                    },
                    "_links": {
                        "self": {
                            "href": "/events/" + str(id)
                        },
                        "previous": {
                            "href": "/events/" + self.__getPreData(id) if self.__getPreData(id) else "null"
                        },
                        "next": {
                            "href": "/events/" + self.__getNextData(id) if self.__getNextData(id) else "null"
                        }
                    }
                }
                return response_data
            else:
                raise Exception("No Data")
        except Exception as e:
            return False
            pass

    def filter(self, data):
        data.order = str(data.order).replace(" ", "+")
        try:
            start = (int(data.page) - 1) * int(data.size)
            sort = 'asc'
            if str(data.order).startswith("+"):
                sort = "asc"
            if str(data.order).startswith("-"):
                sort = "desc"
            print("sort=" + sort)
            column = str(data.order).replace("+", "").replace("-", "")
            # 计算总记录数
            total_sql = f"select {data.filter}  from events order by {column} {sort}"
            total_result = self.db.session.execute(text(total_sql)).all()
            total_page = math.ceil(len(total_result) / int(data.size))
            sql = f"select {data.filter}  from events order by {column} {sort} limit {start},{data.size}"

            result = self.db.session.execute(text(sql)).all()

            arr = []
            for item in result:
                arr.append(item._asdict())

            return {
                "code": 200,
                "data": {
                    "page": data.page,
                    "page-size": data.size,
                    "events": arr,
                    "_links": {
                        "self": f"/events/order={data.order}&page={data.page}&size={data.size}&filter={str(data.filter)}",
                        "next": f"/events/order={data.order}&page={int(data.page) + 1}&size={data.size}&filter={str(data.filter)}" if int(
                            total_page) > int(data.page) else "null"
                    }
                }
            }
        except Exception as e:
            return {
                "code": 400,
                "message": str(e)
            }
            pass

    def statistics(self, data):
        try:
            # 判断提交参数
            if data.format not in ['json', 'image']:
                raise Exception("format must  be json or  image")
            if data.format == 'json':
                return {
                    "code": 200,
                    "data": {
                        "total": self.__getTotalCount(),
                        "pre-days": self.__getDayCount(),
                        "total-current-week": self.__getCurrentWeek(),
                        "total-current-month": self.__getCurrentMonth(),
                    }
                }
                pass
            if data.format == 'image':
                data = {
                    "total": self.__getTotalCount(),
                    "pre-days": self.__getDayCount(),
                    "total-current-week": self.__getCurrentWeek(),
                    "total-current-month": self.__getCurrentMonth()
                }
                return {
                    "code": 200,
                    "data": self.__getImageData(data)
                }
        except Exception as e:
            return {
                "code": 400,
                "message": str(e)
            }
        pass

    def __getImageData(self, data):
        # Get the x and y values for the bar chart
        labels = list(data["pre-days"].keys())
        values = list(data["pre-days"].values())

        # Create the bar chart
        fig, ax = plt.subplots()
        ax.bar(labels, values)

        # Set the chart title and axis labels
        ax.set_title(f"total={data.get('total')},total-current-week={data.get('total-current-week')},total-current-month={data.get('total-current-month')}")
        ax.set_xlabel("Date")
        ax.set_ylabel("Number of events")

        # Save the chart as a PNG in memory
        buffer = io.BytesIO()
        fig.savefig(buffer, format="png")
        buffer.seek(0)
        return buffer
        pass

    def __getCurrentWeek(self):
        now = datetime.now()
        day = now.strftime("%Y-%m-%d")
        today = datetime.today()
        weekday = today.weekday() + 1
        print(day)
        start_date = int(to_timestamp(day + " 00:00:00", fmt="%Y-%m-%d %H:%M:%S"))
        end_date = start_date + (3600 * 24 * (7 - weekday))
        sql = f"select count(*) as count  from events  where _date>={start_date} and _date<={end_date}"
        resutl = self.db.session.execute(text(sql)).all()
        if len(resutl) == 0:
            return 0
        print(resutl[0].count)
        return resutl[0].count
        pass

    def __getCurrentMonth(self):
        now = datetime.now()
        year_month = now.strftime("%Y-%m")
        sql = f"SELECT strftime('%Y-%m', datetime(_date, 'unixepoch')) AS month, COUNT(*) AS count FROM events where month='{year_month}' GROUP BY 1 ORDER BY 1"
        print(sql)
        resutl = self.db.session.execute(text(sql)).all()
        print(resutl)
        if len(resutl) == 0:
            return 0
        print(resutl[0].count)
        return resutl[0].count
        pass

    def __getTotalCount(self):
        sql = "SELECT COUNT(*)  as count FROM events"
        resutl = self.db.session.execute(text(sql)).all()
        if len(resutl) == 0:
            return 0
        return resutl[0].count
        pass

    def __getDayCount(self):
        sql = "SELECT date, COUNT(*) as count FROM events GROUP BY date ORDER BY date"
        resutl = self.db.session.execute(text(sql)).all()
        data = {}
        for item in resutl:
            data[item.date] = item.count
        return data
        pass

    # 获取上一条数据
    def __getPreData(self, id):
        # 原生查询语句select *  from  events where id<27 ORDER BY id desc  LIMIT 1
        stmt = self.db.select(EventModel).filter(EventModel.id < id).order_by(EventModel.id.desc()).limit(1)
        result = self.db.session.execute(stmt).scalar_one_or_none()
        if result:
            return str(result.id)
        else:
            return None

    # 获取下一条数据
    def __getNextData(self, id):
        stmt = self.db.select(EventModel).filter(EventModel.id > id).order_by(EventModel.id.asc()).limit(1)
        result = self.db.session.execute(stmt).scalar_one_or_none()
        if result:
            return str(result.id)
        else:
            return None
        return

    # 日期冲突检
    def __checkDateCross(self, obj={}):
        from_time = int(to_timestamp(obj.get('date') + " " + obj.get("from")))
        to_time = int(to_timestamp(obj.get('date') + " " + obj.get("to")))
        # 开始时间不能大于结束时间
        if from_time > to_time:
            return False

        sql = f"SELECT *  FROM events WHERE date='{obj.get('date')}'"
        sql = sql + f" and ( `from` == {from_time} AND `to` == {to_time} ) OR ( `from` > {from_time} AND `to` < {to_time} ) OR ( `from` < {from_time} AND `to` > {to_time} ) OR ( `to` > {from_time} AND `to` <{to_time} )"

        result = self.db.session.execute(text(sql)).all()

        if len(result) > 0:
            return True
        else:
            return False
        pass

    # 日期冲突检测排除自己
    def __checkDateCross1(self, obj={}):
        from_time = int(to_timestamp(obj.get('date') + " " + obj.get("from")))
        to_time = int(to_timestamp(obj.get('date') + " " + obj.get("to")))
        # 开始时间不能大于结束时间
        if from_time > to_time:
            return False
        sql = f"SELECT * FROM events WHERE date='{obj.get('date')}'"
        sql = sql + f" and id!={obj.get('id')} and ( `from` == {from_time} AND `to` == {to_time} ) and ( `from` > {from_time} AND `to` < {to_time} ) OR ( `from` < {from_time} AND `to` >= {to_time} ) OR ( `to` > {from_time} AND `to` <{to_time} )"

        result = self.db.session.execute(text(sql)).all()

        if len(result) > 0:
            return True
        else:
            return False
        pass
